package com.css.fxfzypg.modules.resultManagement.reportManagement.repository.Impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.resultManagement.reportManagement.VO.ReportInfoVO;
import com.css.fxfzypg.modules.resultManagement.reportManagement.VO.ReportManagementVO;
import com.css.fxfzypg.modules.resultManagement.reportManagement.entity.HistoryUploadReport;
import com.css.fxfzypg.modules.resultManagement.reportManagement.entity.ReportManagement;
import com.css.fxfzypg.modules.resultManagement.reportManagement.repository.ReportManagementRepository;
import com.css.fxfzypg.modules.resultManagement.reportManagement.ypg.SeismicCapacityAnalysis;
import com.css.fxfzypg.modules.resultupload.repository.entity.PrefectureResultEntity;
import com.css.fxfzypg.modules.resultupload.repository.entity.ResultEntity;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.*;

/**
 * @author Dbj
 * @date 2022年10月08日  14:47
 */
@Slf4j
@Repository
public class ReportManagementRepositoryImpl implements ReportManagementRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    SUserService sUserService;

    @Override
    public Map<String, Object> getHistoryReportByTaskId(String taskId, int curPage, int pageSize) {
        String sql = "select * from ypg_preassess_task_history_report  where task_id='" + taskId + "' order by upload_time desc";
        int firstIndex = (curPage - 1) * pageSize;
        String limitSql = " limit " + pageSize + " offset " + firstIndex;
        List<HistoryUploadReport> list = jdbcTemplate.query(sql + limitSql, new BeanPropertyRowMapper<>(HistoryUploadReport.class));
        String totalSql = "select count(*) from ypg_preassess_task_history_report  where task_id='" + taskId + "'";
        Integer total = jdbcTemplate.queryForObject(totalSql, Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public Map<String, Object> getHistoryReportByTaskId(String taskId, int curPage, int pageSize, String type) {
        String sql = "select * from ypg_preassess_task_history_report  where task_id='" + taskId + "' and type = '" + type + "' order by upload_time desc";
        int firstIndex = (curPage - 1) * pageSize;
        String limitSql = " limit " + pageSize + " offset " + firstIndex;
        List<HistoryUploadReport> list = jdbcTemplate.query(sql + limitSql, new BeanPropertyRowMapper<>(HistoryUploadReport.class));
        String totalSql = "select count(*) from ypg_preassess_task_history_report  where task_id='" + taskId + "' and type = '" + type + "'";
        Integer total = jdbcTemplate.queryForObject(totalSql, Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public Map<String, Object> findParentTask(String parentId, ReportManagementVO vo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select DISTINCT t1.create_time, t1.id,t1.task_num,task_name,t1.task_status,t1.parent_id,t1.group_name,t1.group_leader,t1.task_district,t1.report_id,t2.report_status from ypg_preassess_task t1 left join ypg_preassess_task_report t2 on t1.report_id = t2.id left join ypg_group_user t3 on t1.group_id = t3.group_id ");
//        whereSql.append(" and t1.task_manager_id = '" + userId + "'");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t1.parent_id = '" + parentId + "' and (t1.task_manager_id = '" + vo.getCreateUser() + "' or t1.create_user = '" + vo.getCreateUser() + "' or t1.group_leader_id = '" + vo.getCreateUser() + "' or t3.user_id = '" + vo.getCreateUser() + "' ) ");//and (t2.review_status='1' or t2.review_status is null)
        StringBuilder orderSql = new StringBuilder(" order by t1.create_time desc");//reportStatus
        int firstIndex = (curPage - 1) * pageSize;
       // String userId = PlatformSessionContext.getUserID();
//        String user = sUserService.getSUser(userId);
//        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
//        String role = sysRoleService.getRoleByUserId(userId);
//        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
//        for(Map roleMap:roleMaps){
//            str.append(roleMap.get("roleCode")+",");
//        }
//        String roles = str.toString();
//        if(!roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) && !roles.contains(FxfzConstants.YPG_PRI_GL_ROLE)){
//            whereSql.append(" and t1.create_user = '"+userId+"'");
//        }else if(roles.contains(FxfzConstants.YPG_PRI_GL_ROLE)){
//            String areaNameByAreaId = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
//            if(!PlatformObjectUtils.isEmpty(areaNameByAreaId)){
//                whereSql.append(" and t1.task_district like '%"+areaNameByAreaId+"%'");
//            }
//        }

        StringBuilder limitSql = new StringBuilder(" limit " + pageSize + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(vo.getTaskNum())) {
            whereSql.append(" and t1.task_num like '%" + vo.getTaskNum() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getTaskName())) {
            whereSql.append(" and t1.task_name like '%" + vo.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getTaskStatus())) {
            whereSql.append(" and t1.task_status = '" + vo.getTaskStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getProvince()) || !PlatformObjectUtils.isEmpty(vo.getCity()) || !PlatformObjectUtils.isEmpty(vo.getCounty())) {
            //whereSql.append(" and (");
            if (!PlatformObjectUtils.isEmpty(vo.getProvince())) {
                whereSql.append(" and t1.task_district like '%" + vo.getProvince() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(vo.getCity())) {
                whereSql.append(" and t1.task_district like '%" + vo.getCity() + "%'");
            }
            //whereSql.append(") ");
            if (!PlatformObjectUtils.isEmpty(vo.getCounty())) {
                whereSql.append(" and t1.task_district like '%" + vo.getCounty() + "%'");
            }

        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ReportManagementVO.class)).size();
        List<ReportManagementVO> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ReportManagementVO.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public ReportManagement getReportByTaskId(String taskId) {
        List<ReportManagement> list = jdbcTemplate.query("select report.* from ypg_preassess_task_report report,ypg_preassess_task task where report.id = task.report_id and task.del_flag='0' and task.id=?", new BeanPropertyRowMapper<>(ReportManagement.class), taskId);
        return list.size() == 0 ? new ReportManagement() : list.get(0);
    }

    @Override
    public void save(ReportManagement reportManagement) {
        jdbcTemplate.update("insert into ypg_preassess_task_report(id,report_status,default_simple_report_name," +
                        "default_minute_report_name,report_path,report_create_user_id,report_create_time,administrative_level," +
                        "local_user_upload_simple_report_name,local_user_upload_minute_report_name,data_source," +
                        "simplified_report_template,de_report_template,user_upload_simple_report_name,user_upload_minute_report_name," +
                        "simple_report_upload_user_id,minute_report_upload_user_id,simple_report_upload_time,minute_report_upload_time) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", reportManagement.getId(), reportManagement.getReportStatus(),
                reportManagement.getDefaultSimpleReportName(), reportManagement.getDefaultMinuteReportName(),
                reportManagement.getReportPath(), reportManagement.getReportCreateUserId(), reportManagement.getReportCreateTime()
                , reportManagement.getAdministrativeLevel(), reportManagement.getLocalUserUploadSimpleReportName(),
                reportManagement.getLocalUserUploadMinuteReportName(), reportManagement.getDataSource(),
                reportManagement.getSimplifiedReportTemplate(), reportManagement.getDeReportTemplate(),
                reportManagement.getUserUploadSimpleReportName(), reportManagement.getUserUploadMinuteReportName(),
                reportManagement.getSimpleReportUploadUserId(), reportManagement.getMinuteReportUploadUserId(),
                reportManagement.getSimpleReportUploadTime(), reportManagement.getMinuteReportUploadTime());
    }

    @Override
    public void update(ReportManagement reportManagement) {
        StringBuilder builder = new StringBuilder("update ypg_preassess_task_report set ");
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getReportStatus())) {
            builder.append(" report_status='").append(reportManagement.getReportStatus()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getDefaultSimpleReportName())) {
            builder.append(" default_simple_report_name='").append(reportManagement.getDefaultSimpleReportName()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getDefaultMinuteReportName())) {
            builder.append(" default_minute_report_name='").append(reportManagement.getDefaultMinuteReportName()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getUserUploadSimpleReportName())) {
            builder.append(" user_upload_simple_report_name='").append(reportManagement.getUserUploadSimpleReportName()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getUserUploadMinuteReportName())) {
            builder.append(" user_upload_minute_report_name='").append(reportManagement.getUserUploadMinuteReportName()).append("',");
        }

        if (PlatformObjectUtils.isNotEmpty(reportManagement.getLocalUserUploadSimpleReportName())) {
            builder.append(" local_user_upload_simple_report_name='").append(reportManagement.getLocalUserUploadSimpleReportName()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getLocalUserUploadMinuteReportName())) {
            builder.append(" local_user_upload_minute_report_name='").append(reportManagement.getLocalUserUploadMinuteReportName()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getSimpleReportUploadUserId())) {
            builder.append(" simple_report_upload_user_id='").append(reportManagement.getSimpleReportUploadUserId()).append("',");
        }

        if (PlatformObjectUtils.isNotEmpty(reportManagement.getSimpleReportUploadTime())) {
            builder.append(" simple_report_upload_time='").append(reportManagement.getSimpleReportUploadTime()).append("',");
        }

        if (PlatformObjectUtils.isNotEmpty(reportManagement.getMinuteReportUploadUserId())) {
            builder.append(" minute_report_upload_user_id='").append(reportManagement.getMinuteReportUploadUserId()).append("',");
        }

        if (PlatformObjectUtils.isNotEmpty(reportManagement.getMinuteReportUploadTime())) {
            builder.append(" minute_report_upload_time='").append(reportManagement.getMinuteReportUploadTime()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getDataSource())) {
            builder.append(" data_source='").append(reportManagement.getDataSource()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getSimplifiedReportTemplate())) {
            builder.append(" simplified_report_template='").append(reportManagement.getSimplifiedReportTemplate()).append("',");
        }
        if (PlatformObjectUtils.isNotEmpty(reportManagement.getDeReportTemplate())) {
            builder.append(" de_report_template='").append(reportManagement.getDeReportTemplate()).append("',");
        }
        String sql = builder.toString();
//        String substring = sql.substring(sql.length() - 1, sql.length());
        if (",".equals(sql.substring(sql.length() - 1, sql.length()))) {
            sql = sql.substring(0, sql.length() - 1);
        }
        sql += (" where id='" + reportManagement.getId() + "'");
        jdbcTemplate.update(sql);
    }

    @Override
    public ReportManagement getReportById(String id) {
        List<ReportManagement> list = jdbcTemplate.query("select * from ypg_preassess_task_report  where id =?", new BeanPropertyRowMapper<>(ReportManagement.class), id);
        return list.size() == 0 ? null : list.get(0);
    }

    @Override
    public void saveHistoryUploadReport(HistoryUploadReport historyUploadReport) {
        jdbcTemplate.update("insert into ypg_preassess_task_history_report(id,task_id,report_name,upload_user_id,upload_user_name,upload_time,org_id,org_name,type,report_path,local_report_name) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?)", historyUploadReport.getId(), historyUploadReport.getTaskId(), historyUploadReport.getReportName(), historyUploadReport.getUploadUserId(),
                historyUploadReport.getUploadUserName(), historyUploadReport.getUploadTime(), historyUploadReport.getOrgId(), historyUploadReport.getOrgName(), historyUploadReport.getType(), historyUploadReport.getReportPath(), historyUploadReport.getLocalReportName());
    }


    @Override
    public HistoryUploadReport getHistoryUploadReportById(String reportId) {
        List<HistoryUploadReport> list = jdbcTemplate.query("select * from ypg_preassess_task_history_report  where id =?", new BeanPropertyRowMapper<>(HistoryUploadReport.class), reportId);
        return list.size() == 0 ? null : list.get(0);
    }

    @Override
    public void submitReview(String reportId) {
        String sql = "update ypg_preassess_task_report set report_status = ?,report_check_time = ?  where id = ?";
        jdbcTemplate.update(sql, "3", new Date(), reportId);
    }

    @Override
    public ReportManagement getReport(String id, String reportStatus, String reviewStatus) {
        List<ReportManagement> list = jdbcTemplate.query("select * from ypg_preassess_task_report  where id =? and report_status=? and review_status=? ", new BeanPropertyRowMapper<>(ReportManagement.class), id, reportStatus, reviewStatus);
        return list.size() == 0 ? null : list.get(0);
    }

    @Override
    public List<Map<String, Object>> getSubArea(String taskId) {
        String sql = "select c.name as province,b.name as city,a.name as area" +
                " from district_boundary_county a" +
                " left join district_boundary_city b on b.code=concat(subString(a.code,0,5),'00')" +
                "left join district_boundary_province c on c.code=concat(subString(a.code,0,3),'0000')," +
                " (select geom FROM ypg_preassess_task WHERE id = ?) t" +
                " where ST_Intersects(a.geom,t.geom)" +
                "and ST_Touches(a.geom,t.geom)!='t'" +
                "order by a.code,b.code,c.code";

        return jdbcTemplate.queryForList(sql, taskId);
    }

    @Override
    public List<Map<String, Object>> getSubArea(List<String> taskIdList) {
        String sql = "select c.name as province,b.name as city,a.name as area" +
                " from district_boundary_county a" +
                " left join district_boundary_city b on b.code=concat(subString(a.code,0,5),'00')" +
                "left join district_boundary_province c on c.code=concat(subString(a.code,0,3),'0000')," +
                " (select geom FROM ypg_preassess_task WHERE id in (:taskIdList)) t" +
                " where ST_Intersects(a.geom,t.geom)" +
                "and ST_Touches(a.geom,t.geom)!='t'" +
                "order by a.code,b.code,c.code";

        Map idMap = new HashMap();
        idMap.put("taskIdList", taskIdList);
        return namedParameterJdbcTemplate.queryForList(sql, idMap);
    }


    @Override
    public String getReviewOpinion(String reportId) {
        try {
            String sql = "select t2.review_opinion from ypg_preassess_task_report t1 left join ypg_preassess_task_report_record t2  on t1.id=t2.report_id  where t1.report_status!='4' and t2.review_status='1' and  t2.report_id='" + reportId + "' order by review_time desc limit 1";
            String reviewOpinion = jdbcTemplate.queryForObject(sql, String.class);
            return reviewOpinion;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<ReportInfoVO> getReportInfo() {
        String sql = "select a.*,b.task_name,b.task_district from ypg_preassess_task_report a left join ypg_preassess_task b on a.id=b.report_id where b.del_flag='0' and report_status='4'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ReportInfoVO.class));
    }

    @Override
    public Boolean getSTIntersects(String geomText, String geomText1) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext(?,4490))";
        return jdbcTemplate.queryForObject(sql, Boolean.class, geomText, geomText1);
    }
}
